{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 独立使用django model（一）\n",
    "# Django Shell\n",
    "# 项目根目录中打开Django Shell : python manage.py shell\n",
    "# import django\n",
    "# django.setup()  # 装载Django\n",
    "\n",
    "# 独立使用django model（二）\n",
    "# python ×××.py or Jupyter Notebook\n",
    "# 将脚本放置在项目根目录下jupyter_notebook文件中比较方便\n",
    "import sys\n",
    "import os\n",
    "import json\n",
    "import requests\n",
    "from django.core.files import File\n",
    "from django.core.files.base import ContentFile\n",
    "# 外部脚本链接django项目\n",
    "# 添加环境变量\n",
    "'''\n",
    "print(os.path.abspath('__file__'))\n",
    "print(os.path.dirname(os.path.abspath('__file__')))\n",
    "print(os.path.dirname(os.path.dirname(os.path.abspath('__file__'))))\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook/__file__\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis\n",
    "'''\n",
    "project = os.path.dirname(os.getcwd())  # get current work directory\n",
    "sys.path.append(project)\n",
    "sys.path.append(os.path.join(project, 'rmis'))\n",
    "# sys.path.append已设置临时环境变量，可以直接调用其中的文件，脚本是外部脚本，只是放在了项目当中而已\n",
    "os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')\n",
    "# 相关数据库配置等都在settings.py文件中\n",
    "# 导入并装载django\n",
    "import django\n",
    "django.setup()\n",
    "# 脚本正文\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[9, 5]\n"
     ]
    }
   ],
   "source": [
    "from datetime import date, datetime, timedelta\n",
    "import pandas as pd\n",
    "import random\n",
    "from invoice.models import Expense\n",
    "from organization.models import Store \n",
    "\n",
    "stores = [ store.id for store in Store.objects.filter(is_own=\"00\")]\n",
    "print(stores)\n",
    "\n",
    "EXPENSE_TYPE = (\n",
    "    ('00', '房租'),  # 假设每月月初\n",
    "    ('01', '工资'),  # 假设每月月初\n",
    "    ('02', '水电'),  # 假设每月月初\n",
    "    ('03', '装修'),  # 假设每年年初\n",
    "    ('04', '差旅'),  # 假设每月月中和月末\n",
    "    ('05', '其他'),  # 假设每月月初\n",
    ")\n",
    "\n",
    "start = '1/1/2017'\n",
    "end = '5/18/2019'\n",
    "idx01= pd.date_range(start=start, end=end,freq=\"MS\").to_list()  # 每月月初\n",
    "s01  = pd.Series(idx01)\n",
    "idx02= pd.date_range(start=start, end=end,freq=\"SM\").to_list()  # 每月月中和月末\n",
    "s02  = pd.Series(idx02)\n",
    "idx03= pd.date_range(start=start, end=end,freq=\"YS\").to_list()  # 每月月初\n",
    "s03  = pd.Series(idx03)\n",
    "\n",
    "expense = []\n",
    "for store in stores:    \n",
    "    for d in s01.dt.to_pydatetime().tolist():\n",
    "        e = dict()\n",
    "        e[\"amount\"] = random.choice([3000,3500,4000,4500,5000])\n",
    "        e[\"type\"] = \"01\"\n",
    "        e[\"created\"] = d\n",
    "        e[\"fk_store\"] = store\n",
    "        expense.append(e)\n",
    "        e = dict()\n",
    "        e[\"amount\"] = random.choice([3000,3500,4000,4500,5000])\n",
    "        e[\"type\"] = \"01\"\n",
    "        e[\"created\"] = d\n",
    "        e[\"fk_store\"] = store\n",
    "        expense.append(e)\n",
    "        \n",
    "        e = dict()\n",
    "        e[\"amount\"] = random.choice([5000,5500,6000])\n",
    "        e[\"type\"] = \"00\"\n",
    "        e[\"created\"] = d\n",
    "        e[\"fk_store\"] = store\n",
    "        expense.append(e)\n",
    "        \n",
    "        e = dict()\n",
    "        e[\"amount\"] = random.choice([300,350,400])\n",
    "        e[\"type\"] = \"02\"\n",
    "        e[\"created\"] = d\n",
    "        e[\"fk_store\"] = store\n",
    "        expense.append(e)\n",
    "        \n",
    "        e = dict()\n",
    "        e[\"amount\"] = random.choice([100,200,300])\n",
    "        e[\"type\"] = \"05\"\n",
    "        e[\"created\"] = d\n",
    "        e[\"fk_store\"] = store\n",
    "        expense.append(e)\n",
    "    for d in s02.dt.to_pydatetime().tolist():\n",
    "        e = dict()\n",
    "        e[\"amount\"] = random.choice([3000,3500,4000,4500,5000])\n",
    "        e[\"type\"] = \"04\"\n",
    "        e[\"created\"] = d\n",
    "        e[\"fk_store\"] = store\n",
    "        expense.append(e)\n",
    "    for d in s03.dt.to_pydatetime().tolist():\n",
    "        e = dict()\n",
    "        e[\"amount\"] = random.choice([15000,20000])\n",
    "        e[\"type\"] = \"03\"\n",
    "        e[\"created\"] = d\n",
    "        e[\"fk_store\"] = store\n",
    "        expense.append(e)\n",
    "\n",
    "df = pd.DataFrame.from_records(expense)\n",
    "df.to_excel(\"./excel/invoice.expense.20170101.20190518.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
